Skip Headers

Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# TRIGGERASSIGN

Within a program triggered by an Assign event for an object, assigns a value to the triggering object.

Note: The USETRIGGERS option must be set to its default value of `TRUE` for a TRIGGERASSIGN to execute

See: "Trigger Programs".

Data type

The data type of the object to which Oracle OLAP assigns the value.

Syntax

TRIGGERASSIGN value

Arguments

value

  The value that you want assigned.

Examples

Example 24-14 Assigning an Alternative Value using an Assign Trigger

Assume that you have objects with the following descriptions in your analytic workspace.

    DEFINE GEOG.D DIMENSION TEXT
    DEFINE TIME.D DIMENSION TEXT
    DEFINE TIME.PARENTREL RELATION TIME.D <TIME.D>
    DEFINE SALES VARIABLE DECIMAL <GEOG.D TIME.D>
    DEFINE MODIFIED_SALES VARIABLE DECIMAL <GEOG.D TIME.D>

Assume also that you have populated the `sales` variable with the values shown in the following report, but that you have not yet populated the `modified_sales` variable.

    -----------------------SALES-----------------------
                 ----------------------GEOG.D-----------------------
    TIME.D          Boston      Medford     San Diego    Sunnydale
    ------------ ------------ ------------ ------------ ------------
    Jan76            1,000.00     2,000.00     3,000.00     4,000.00
    Feb76            2,000.00     4,000.00     6,000.00     8,000.00
    Mar76            3,000.00     6,000.00     9,000.00    12,000.00
    76Q1                   NA           NA           NA           NA

Now you want to assign values to the `modified_sales` variable using various expressions, however, you want to ensure that the values never are less than or equal to 1,000. You can assure this processing by taking the following steps:

1. Create the following program that checks for values less than or equal to 1000 condition.

       DEFINE TRIGGER_ASSIGN_MODIFIED_SALES PROGRAM
       PROGRAM
       ARGUMENT datavalue DECIMAL
       IF datavalue LE 1000
        THEN TRIGGERASSIGN 1000
       show 'description of triggering object = '
       DESCRIBE &TRIGGER(NAME)
       SHOW JOINCHARS ('calltype = ' CALLTYPE)
       SHOW JOINCHARS ('triggering event = ' TRIGGER(EVENT))
       SHOW JOINCHARS ('triggering subevent = ' TRIGGER(SUBEVENT))
       SHOW JOINCHARS ('value passed to program = ' datavalue)
       SHOW '  '
       END

2. Issue the following statements to add an Assign trigger to the `modified_sales` variable. The `trigger_assign_modified_sales` program is the trigger program.

       CONSIDER modified_sales
       TRIGGER ASSIGN trigger_assign_modified_sales

3. Assign values to `modified_sales`.

       modified_sales = sales - 1000

4. This statement triggers the execution of the `trigger_assign_modified_sales` program for each value that Oracle OLAP assigns.

       description of triggering object =
       DEFINE MODIFIED_SALES VARIABLE DECIMAL <GEOG.D TIME.D>
       TRIGGER ASSIGN TRIGGER_ASSIGN_MODIFIED_SALES
       calltype = TRIGGER
       triggering event = ASSIGN
       triggering subevent =
       value passed to program = 0.00

       description of triggering object =
       DEFINE MODIFIED_SALES VARIABLE DECIMAL <GEOG.D TIME.D>
       TRIGGER ASSIGN TRIGGER_ASSIGN_MODIFIED_SALES
       calltype = TRIGGER
       triggering event = ASSIGN
       triggering subevent =
       value passed to program = 1,000.00

       description of triggering object =
       DEFINE MODIFIED_SALES VARIABLE DECIMAL <GEOG.D TIME.D>
       TRIGGER ASSIGN TRIGGER_ASSIGN_MODIFIED_SALES
       calltype = TRIGGER
       triggering event = ASSIGN
       triggering subevent =
       value passed to program = 2,000.00

       description of triggering object =
       DEFINE MODIFIED_SALES VARIABLE DECIMAL <GEOG.D TIME.D>
       TRIGGER ASSIGN TRIGGER_ASSIGN_MODIFIED_SALES
       calltype = TRIGGER
       triggering event = ASSIGN
       triggering subevent =
       value passed to program = 3,000.00

       description of triggering object =
       DEFINE MODIFIED_SALES VARIABLE DECIMAL <GEOG.D TIME.D>
       TRIGGER ASSIGN TRIGGER_ASSIGN_MODIFIED_SALES
       calltype = TRIGGER
       triggering event = ASSIGN
       triggering subevent =
       value passed to program = 1,000.00

       description of triggering object =
       DEFINE MODIFIED_SALES VARIABLE DECIMAL <GEOG.D TIME.D>
       TRIGGER ASSIGN TRIGGER_ASSIGN_MODIFIED_SALES
       calltype = TRIGGER
       triggering event = ASSIGN
       triggering subevent =
       value passed to program = 3,000.00

       description of triggering object =
       DEFINE MODIFIED_SALES VARIABLE DECIMAL <GEOG.D TIME.D>
       TRIGGER ASSIGN TRIGGER_ASSIGN_MODIFIED_SALES
       calltype = TRIGGER
       triggering event = ASSIGN
       triggering subevent =
       value passed to program = 5,000.00

       description of triggering object =
       DEFINE MODIFIED_SALES VARIABLE DECIMAL <GEOG.D TIME.D>
       TRIGGER ASSIGN TRIGGER_ASSIGN_MODIFIED_SALES
       calltype = TRIGGER
       triggering event = ASSIGN
       triggering subevent =
       value passed to program = 7,000.00

       description of triggering object =
       DEFINE MODIFIED_SALES VARIABLE DECIMAL <GEOG.D TIME.D>
       TRIGGER ASSIGN TRIGGER_ASSIGN_MODIFIED_SALES
       calltype = TRIGGER
       triggering event = ASSIGN
       triggering subevent =
       value passed to program = 2,000.00

       description of triggering object =
       DEFINE MODIFIED_SALES VARIABLE DECIMAL <GEOG.D TIME.D>
       TRIGGER ASSIGN TRIGGER_ASSIGN_MODIFIED_SALES
       calltype = TRIGGER
       triggering event = ASSIGN
       triggering subevent =
       value passed to program = 5,000.00

       description of triggering object =
       DEFINE MODIFIED_SALES VARIABLE DECIMAL <GEOG.D TIME.D>
       TRIGGER ASSIGN TRIGGER_ASSIGN_MODIFIED_SALES
       calltype = TRIGGER
       triggering event = ASSIGN
       triggering subevent =
       value passed to program = 8,000.00

       description of triggering object =
       DEFINE MODIFIED_SALES VARIABLE DECIMAL <GEOG.D TIME.D>
       TRIGGER ASSIGN TRIGGER_ASSIGN_MODIFIED_SALES
       calltype = TRIGGER
       triggering event = ASSIGN
       triggering subevent =
       value passed to program = 11,000.00

       description of triggering object =
       DEFINE MODIFIED_SALES VARIABLE DECIMAL <GEOG.D TIME.D>
       TRIGGER ASSIGN TRIGGER_ASSIGN_MODIFIED_SALES
       calltype = TRIGGER
       triggering event = ASSIGN
       triggering subevent =
       value passed to program =

       description of triggering object =
       DEFINE MODIFIED_SALES VARIABLE DECIMAL <GEOG.D TIME.D>
       TRIGGER ASSIGN TRIGGER_ASSIGN_MODIFIED_SALES
       calltype = TRIGGER
       triggering event = ASSIGN
       triggering subevent =
       value passed to program =

       description of triggering object =
       DEFINE MODIFIED_SALES VARIABLE DECIMAL <GEOG.D TIME.D>
       TRIGGER ASSIGN TRIGGER_ASSIGN_MODIFIED_SALES
       calltype = TRIGGER
       triggering event = ASSIGN
       triggering subevent =
       value passed to program =

       description of triggering object =
       DEFINE MODIFIED_SALES VARIABLE DECIMAL <GEOG.D TIME.D>
       TRIGGER ASSIGN TRIGGER_ASSIGN_MODIFIED_SALES
       calltype = TRIGGER
       triggering event = ASSIGN
       triggering subevent =
       value passed to program =

5. The following report of `modified_sales` shows that all values are at least 1,000.

       ------------------MODIFIED_SALES-------------------
                    ----------------------GEOG.D-----------------------
       TIME.D          Boston      Medford     San Diego    Sunnydale
       ------------ ------------ ------------ ------------ ------------
       Jan76            1,000.00     1,000.00     2,000.00     3,000.00
       Feb76            1,000.00     3,000.00     5,000.00     7,000.00
       Mar76            2,000.00     5,000.00     8,000.00    11,000.00
       76Q1                   NA           NA           NA           NA